package com.weipu.common.facade.content;

import java.util.ArrayList;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.text.TextUtils;
import android.util.Log;

import com.weipu.common.util.Logger;

/**
 *  SQLiteOpenHelper 辅助子类
 * 
 * @author  hjd008
 * @version  [版本号, 2014-12-16]
 * @see  [相关类/方法]
 * @since  [产品/模块版本]
 */
class DBHandler extends SQLiteOpenHelper
{
    private static final String TAG = "DBHandler";
    
    /**
     * version==3时统一数据库名 ： dx_pad.db->dx.db
     * 数据文件名
     */
    private static final String DB_NAME = "dx.db";
    
    /**
     * version
     * version 5: 2016.4.5 新增一键过滤表格－－tbl_onekeyfilter by Tony
     */
    private static final int VERSION = 5;
    
    /**
     * 创建表SQL语句
     */
    private static ArrayList<String> initSQLList;
    
    /**
     * 删除表SQL语句
     */
    private static ArrayList<String> destroySQLList;
    
    private Context mContext;
    
    static
    {
        int size = 6;
        initSQLList = new ArrayList<String>();
        initSQLList.add(ContentURI.ContentMyCart.TABLE_CREATE);
        initSQLList.add(ContentURI.ContentSearchHistory.TABLE_CREATE);
        initSQLList.add(ContentURI.ContentBrowsingHistory.TABLE_CREATE);
        initSQLList.add(ContentURI.CategoryList.TABLE_CREATE);
        initSQLList.add(ContentURI.ProductList.TABLE_CREATE);
        initSQLList.add(ContentURI.HotWord.TABLE_CREATE);
        initSQLList.add(ContentURI.OnekeyFilterList.TABLE_CREATE);
        initSQLList.add(ContentURI.HomeModuleData.TABLE_CREATE);
        
        destroySQLList = new ArrayList<String>();
        destroySQLList.add(ContentURI.ContentMyCart.TABLE_DROP);
        destroySQLList.add(ContentURI.ContentSearchHistory.TABLE_DROP);
        destroySQLList.add(ContentURI.ContentBrowsingHistory.TABLE_DROP);
        destroySQLList.add(ContentURI.CategoryList.TABLE_DROP);
        destroySQLList.add(ContentURI.ProductList.TABLE_DROP);
        destroySQLList.add(ContentURI.HotWord.TABLE_DROP);
        destroySQLList.add(ContentURI.OnekeyFilterList.TABLE_DROP);
        destroySQLList.add(ContentURI.HomeModuleData.TABLE_DROP);
    }
    
    public DBHandler(Context context)
    {
        super(context, DB_NAME, null, VERSION);
        this.mContext = context;
    }
    
    @Override
    public void onCreate(SQLiteDatabase db)
    {
        Logger.d(TAG, "enter into onCreate");
        // VERSION == 3 此版本数据库的PAD客户端版本 数据库修改数据库名 @see DB_NAME
        if (TextUtils.equals(mContext.getPackageName(), "com.weipu.dx"))
        {
            mContext.deleteDatabase("dx_pad.db");
        }
        for (String sql : initSQLList)
        {
            db.execSQL(sql);
        }
        Logger.d(TAG, "exit from onCreate");
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {
        // FIXME 更新需要考虑 PAD&Phone 表接口更新，可以直接清除之前所有表和数据库
        Logger.d(TAG, "enter into onUpgrade, destroySQLList  ");
        // 数据库版本由1升级为2
        if (oldVersion <= 1 && newVersion >= 2)
        {
            Logger.d(TAG, "oldVersion:" + oldVersion + "update to  newVersion:" + newVersion);
            //do nothing.
        }
        // 数据库版本由2升级为3,此处pad由于修改数据库名会onCreate,phone会更新这里
        if (oldVersion <= 2 && newVersion >= 3)
        {
            Logger.d(TAG, "oldVersion:" + oldVersion + "update to  newVersion:" + newVersion);
            //清除手机数据库两张表
            db.execSQL("drop table if exists tbl_mycart;");
            db.execSQL("drop table if exists tbl_products;");
            //创建以下6张表
            for (String sql : initSQLList)
            {
                db.execSQL(sql);
            }
        }
        // 数据库版本由3升级为4
        if (oldVersion <= 3 && newVersion >= 4)
        {
            //future task
            Logger.d(TAG, "oldVersion:" + oldVersion + "update to  newVersion:" + newVersion);
          //清除手机数据库两张表
//            db.execSQL("ALTER TABLE tbl_mycart ADD COLUMN isFavorite varchar(50)");
            if(!isExistField(db,"tbl_mycart","mediumPictureURL")){
            	 db.execSQL("ALTER TABLE tbl_mycart ADD COLUMN mediumPictureURL varchar(50)");
            }
            db.execSQL("CREATE TABLE IF NOT EXISTS tbl_mycart (_id integer primary key autoincrement, sku integer,shortHeadLine1 varchar(50),price float,quantity integer ,smallPictureURL varchar(50),freeShipping integer, listprice float,mediumPictureURL varchar(50))");//
           
            
            //创建tbl_mycart表
//            db.execSQL("CREATE TABLE IF NOT EXISTS tbl_mycart (_id integer primary key autoincrement, sku integer,shortHeadLine1 varchar(50),price float,quantity integer ,smallPictureURL varchar(50),freeShipping integer, listprice float,isFavorite varchar(50))");// 
            if(!isExistField(db,"tbl_browsinghistory","mediumPictureURL")){
               db.execSQL("ALTER TABLE tbl_browsinghistory ADD COLUMN mediumPictureURL varchar(50)");
            }
            db.execSQL("CREATE TABLE IF NOT EXISTS tbl_browsinghistory(_id integer primary key autoincrement, sku integer,shortHeadLine1 varchar(50),smallPictureURL varchar(50),price float,oldprice float,reviewCount int,productRating float,mediumPictureURL varchar(50))");
//          create table tbl_browsinghistory(_id integer primary key autoincrement, sku integer,shortHeadLine1 varchar(50),smallPictureURL varchar(50),price float,oldprice float,reviewCount int,productRating float); 
//          for (String sql : initSQLList)
//            {
//                db.execSQL(sql);
//            }
            //            if (null != destroySQLList && null != initSQLList)
            //            {
            //                Logger.d(TAG, "destroySQLList " + destroySQLList.size() + ", initSQLList " + initSQLList.size());
            //                for (String sql : destroySQLList)
            //                {
            //                    db.execSQL(sql);
            //                }
            //                for (String sql : initSQLList)
            //                {
            //                    db.execSQL(sql);
            //                }
            //            }
          
          //TODO SearchHistory表新增了searchTime字段
          
        }
        //Version 5: 4.0版本一键过滤黑白名单本地存储添加
        if (oldVersion <= 4 && newVersion >= 5){
        	//为搜索历史表添加字段
        	 db.execSQL("ALTER TABLE tbl_searchhistory ADD COLUMN searchTime long");
             db.execSQL("CREATE TABLE IF NOT EXISTS tbl_searchhistory(_id integer primary key autoincrement, searchhistoryId integer,historyname varchar(50),searchTime long)");
        	//创建表－跟用户ID相关联的一级分类黑白名单
        	db.execSQL("CREATE TABLE IF NOT EXISTS tbl_onekeyfilter(_id integer primary key autoincrement,customerID long,categoryID integer,categoryName text,isShow varchar(10))");
        
            //创建首页数据表
        	db.execSQL(ContentURI.HomeModuleData.IF_EXISTS_TABLE_CREATE);
            //创建热搜表
        	db.execSQL(ContentURI.HotWord.IF_EXISTS_TABLE_CREATE);
        }
        Logger.d(TAG, "exit from onUpgrade");
    }
    
    /**
     * 判断数据库中指定表的指定字段是否存在
     * @param db
     * @param strTableName 指定表名称
     * @param strFieldName 执行字段名称
     * @return
     */
    private boolean isExistField(SQLiteDatabase db, String strTableName, String strFieldName){
    	StringBuilder builder =new StringBuilder();
    	builder.append("name = '").append(strTableName).append("' AND sql LIKE '%").append(strFieldName).append("%'");
    	Cursor cursor = null;
    	try{
    		cursor = db.query("sqlite_master", null, builder.toString(), null, null, null, null);
        	return cursor.getCount() > 0;
    	} catch (Exception e){
    		e.printStackTrace();
    	} finally {
    		if(cursor != null){
    			cursor.close();
    		}
    	}
    	return false;
    }
    
}
